SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE proc [dbo].[Drug_EndAmountDetail]
@GroupId int,@DrugId nvarchar(20),@fromdate datetime,@todate datetime
as
begin
	--- end amount detail
	SELECT * from dbo.EndAmountDetail(@GroupId,@DrugId,@fromdate,@todate)
	-- detail in drug
	SELECT GD.GrnId,GrnDate,Quantity,Unitprice,ExpDate,Batchlot,GD.Remark  from GRNDETAILS AS GD INNER JOIN GRN G ON G.GrnId=GD.GrnId 
	WHERE GrnDate < @fromdate and Drugid=@DrugId and G.IsComplete=1
	ORDER BY drugid
	--detail out drug
	SELECT  PD.PrescriptionID,CreateDate,Quantity,UnitPrice,ExpDate,BatchLot,PD.Remark 
	FROM dbo.PrescriptionDetail AS PD INNER JOIN dbo.Prescription P ON P.PrescriptionID=PD.PrescriptionID
	WHERE DeliveryDate < @fromdate and Drugid=@DrugId
	order by drugid
	-- adjust detail
	SELECT  SD.StockOutId,StockOutDate,Quantity,UnitPrice,ExpDate,BatchLot,SD.Remark
	FROM dbo.StockOutDetails AS SD INNER JOIN dbo.StockOut S ON S.StockOutId=SD.StockOutId
	WHERE StockOutDate < @fromdate and Drugid=@DrugId and S.IsComplete=1
	ORDER BY drugid

end

GO
